Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports VNDPOS.Config

Namespace VNDPOS.Helper
    Public Class DBHelper
        Public Shared Function GetSqlConnection1() As SqlConnection
            If Not CONFIGAPP.WEBAPP Then
                Try
                    Dim xmldoc = XDocument.Load(System.Environment.CurrentDirectory & "//" & CONSTANT.CONFIG_FILENAME)
                    If CONFIGAPP.LOCATION_ID = "" Then
                        CONFIGAPP.LOCATION_ID = xmldoc.Elements().<Connection>.<LocationID>(0)
                        CONFIGAPP.LOCATION_ID = CryptoHelper.DecryptData(CONFIGAPP.LOCATION_ID)

                        CONFIGAPP.LOCATION_USER = xmldoc.Elements().<Connection>.<LocationUsername>(0)
                        CONFIGAPP.LOCATION_USER = CryptoHelper.DecryptData(CONFIGAPP.LOCATION_USER)

                        CONFIGAPP.LOCATION_PASS = xmldoc.Elements().<Connection>.<LocationUserPass>(0)
                        CONFIGAPP.LOCATION_PASS = CryptoHelper.DecryptData(CONFIGAPP.LOCATION_PASS)

                        CONFIGAPP.DATABASE_SERVER = xmldoc.Elements().<Connection>.<DatabaseServer>(0)
                        CONFIGAPP.DATABASE_SERVER = CryptoHelper.DecryptData(CONFIGAPP.DATABASE_SERVER)

                        CONFIGAPP.DATABASE_NAME = xmldoc.Elements().<Connection>.<DatabaseName>(0)
                        CONFIGAPP.DATABASE_NAME = CryptoHelper.DecryptData(CONFIGAPP.DATABASE_NAME)

                        CONFIGAPP.DATABASE_USERNAME = xmldoc.Elements().<Connection>.<DatabaseUsername>(0)
                        CONFIGAPP.DATABASE_USERNAME = CryptoHelper.DecryptData(CONFIGAPP.DATABASE_USERNAME)

                        CONFIGAPP.DATABASE_PWD = xmldoc.Elements().<Connection>.<DatabasePassword>(0)
                        CONFIGAPP.DATABASE_PWD = CryptoHelper.DecryptData(CONFIGAPP.DATABASE_PWD)

                        CONFIGAPP.PRINTER_NAME = xmldoc.Elements().<Connection>.<PrinterName>(0)
                        CONFIGAPP.PRINTER_NAME = CryptoHelper.DecryptData(CONFIGAPP.PRINTER_NAME)

                        Dim MainCounter As String = xmldoc.Elements().<Connection>.<MainCounter>(0)
                        CONFIGAPP.SYNCHRONIZE = MainCounter
                    End If

                    Return GetSqlConnection(GetConnectionString(CONFIGAPP.DATABASE_SERVER, CONFIGAPP.DATABASE_NAME, CONFIGAPP.DATABASE_USERNAME, CONFIGAPP.DATABASE_PWD))
                Catch Ex As Exception
                    Throw New Exception(Ex.Message, Ex)
                Finally
                End Try
            End If
            Return GetSqlConnection(CONFIGAPP.DB_STRING_1)
        End Function
        Public Shared Function GetSqlConnection2() As SqlConnection
            Return GetSqlConnection(CONFIGAPP.DB_STRING_2)
        End Function
        Private Shared Function GetConnectionString(ByVal IpAddress As String, ByVal DB As String, ByVal UserName As String, ByVal Password As String) As String
            Return "Data Source=" & IpAddress & ";Initial Catalog=" & DB & ";Persist Security Info=True;User ID=" & UserName & "; Password=" & Password & ""
        End Function
        Public Shared Function GetSqlConnection(ByVal connectString As String) As SqlConnection
            Try
                Dim Connection As New SqlConnection(connectString)
                'Connection.Open()
                Return Connection
            Catch Ex As System.Data.SqlClient.SqlException
            Finally
            End Try

            Return Nothing
        End Function
        Public Shared Function GetConnection1() As SqlConnection
            Dim dbconnection As SqlConnection = GetSqlConnection1()
            dbconnection.Open()
            Return dbconnection
        End Function
        Public Shared Function GetConnection2() As SqlConnection
            Dim dbconnection As SqlConnection = GetSqlConnection2()
            dbconnection.Open()
            Return dbconnection
        End Function
        Public Shared Function GetConnection(ByVal IpAddress As String, ByVal DB As String, ByVal UserName As String, ByVal Password As String) As SqlConnection
            Dim conStr As String = GetConnectionString(IpAddress, DB, UserName, Password)
            Dim dbconnection As SqlConnection = GetSqlConnection(conStr)
            dbconnection.Open()
            Return dbconnection
        End Function
        Public Shared Function GetDataSetFromSQL(ByVal sql As String, ByVal lParamArray As SqlParameter()) As DataSet
            Dim _list As DataSet = Nothing
            Try
                Using con As SqlConnection = DBHelper.GetConnection1()
                    _list = SqlHelper.ExecuteDataset(con, CommandType.Text, sql, lParamArray)
                End Using
            Catch ex As Exception
                Throw New Exception(ex.Message, ex)
            Finally
            End Try
            Return _list
        End Function
        Public Shared Function ReadValue(ByVal dr As SqlDataReader) As ArrayList
            Dim strValue As New ArrayList
            Dim i = 0
            Try
                For i = 0 To dr.FieldCount - 1
                    If Not IsDBNull(dr(i)) Then
                        strValue.Add(dr(i).ToString())
                    Else
                        strValue.Add(Nothing)
                    End If
                Next i
            Catch ex As Exception
                Throw New Exception(ex.Message.ToString, ex)
            Finally
            End Try
            Return strValue
        End Function
    End Class
End Namespace
